package org.cafeanimal.sqlite;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Deque;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

import com.google.common.base.Optional;
import com.google.common.collect.Table;

import org.cafeanimal.sqlite.util.BP;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Represents a physical SQLite database and hides all of the JDBC stuff
 * from client code.
 * 
 * @author michael
 */
public class Database {
    
    private static final Logger LOGGER = LoggerFactory.getLogger(Database.class);

    private final File dbPath;
    
    private final Lock connectionLock;
    private final Deque<Connection> connectionPool;
    private final List<TableDef> tableDefs;
    

    private Database(File dbPath, List<TableDef> tableDefs) throws SQLException {
        this.dbPath = dbPath;
        this.tableDefs = tableDefs;
        this.connectionLock = new ReentrantLock();
        this.connectionPool = new LinkedList<Connection>();
        
        createTables();
    }

    public static Optional<Database> create(File dbPath, TableDef tableDef) {
        return create(dbPath, Collections.singletonList(tableDef));
    }
    
    public static Optional<Database> create(File dbPath, List<TableDef> tableDefs) {
        BP.notNull("dbPath", dbPath);
        BP.collectionNotNullOrEmpty("tableDefs", tableDefs);
        
        Database db = null;
        try {
            // Ensure SQLite driver is loaded
            Class.forName("org.sqlite.JDBC");
            
            List<TableDef> copyDefs = copyTableDefs(tableDefs);

            db = new Database(dbPath, copyDefs);
            
            
        } catch (ClassNotFoundException ex) {
            LOGGER.error("Unable to initialize SQLite driver");
            
        } catch (SQLException ex) {
            LOGGER.error("Unable to create database");
        
        } finally {
            return Optional.of(db);
        }
    }
    
    private static List<TableDef> copyTableDefs(List<TableDef> tableDefs) {
        List<TableDef> copy = new ArrayList<TableDef>();
        
        for (TableDef tableDef : tableDefs) {
            copy.add(new TableDef(tableDef));
        }
        
        return copy;
    }


    public void close() {
        try {
            closeConnections();
        } catch (SQLException ex) {
            LOGGER.error(ex.getLocalizedMessage());
        }
    }
    
    /**
     * Insert a new row into a table. The supplied values must match the 
     * number of columns in the table and be compatible in type.
     * 
     * @param tableName table name
     * @param values values for all columns
     * 
     * @return true if successful
     */
    public boolean insert(String tableName, Object[] values) {
        Optional<TableDef> opDef = getTableDef(tableName);
        if (!opDef.isPresent()) {
            LOGGER.error("No table found with this name: {}", tableName);
            return false;
        }
        
        TableDef tblDef = opDef.get();
        boolean success = true;
        
        try {
            Optional<Map<String, Object>> opMap = createValueMap(tblDef, values);
            if (opMap.isPresent()) {
                doInsert(tableName, opMap.get());
            }

        } catch (SQLException ex) {
            LOGGER.error("Error updating table:", ex);
            success = false;

        } finally {
            return success;
        }
    }
    
    /**
     * Inserts a new row into a table.
     * 
     * @param tableName table name
     * @param values map of field name : value pairs
     * 
     * @return true if successful
     */
    public boolean insert(String tableName, Map<String, Object> valueMap) {
        BP.stringNotNullOrEmpty("tableName", tableName);
        BP.collectionNotNullOrEmpty("valueMap", valueMap);
        
        Optional<TableDef> op = getTableDef(tableName);
        if (!op.isPresent()) {
            return false;
        }
        
        TableDef tblDef = op.get();
        if (!checkValuesAgainstTableDef(tblDef, valueMap)) {
            return false;
        }
        
        boolean success = true;
        try {
            doInsert(tableName, valueMap);

        } catch (Exception ex) {
            LOGGER.error("Error updating table:", ex);
            success = false;

        } finally {
            return success;
        }
    }
    
    /**
     * Inserts the contents of the input {@linkplain Table} (Guava collection class)
     * into the given database table. This method wraps the individual record inserts
     * inside a transaction.
     * <p>
     * Input table column names must match database table column names. Input table
     * row names are user-defined and are only used for error reporting, however they
     * must be unique.
     * 
     * @param tableName table name
     * @param data data to be inserted
     * 
     * @return count of records inserted
     */
    public int insert(String tableName, Table<Object, String, Object> data) {
        Connection con = null;
        boolean commit = true;
        int count = 0;

        Optional<TableDef> op = getTableDef(tableName);
        if (!op.isPresent()) {
            return 0;
        }
        
        TableDef tblDef = op.get();
        
        try {
            con = getConnection();
            con.setAutoCommit(false);
            
            for (Object rowKey : data.rowKeySet()) {
                Map<String, Object> valueMap = data.row(rowKey);
                if (!checkValuesAgainstTableDef(tblDef, valueMap)) {
                    commit = false;
                    break;
                }
                
                doInsertRec(con, tableName, valueMap);
                count++ ;
            }
            
        } catch (SQLException ex) {
            con.rollback();
            commit = false;
            count = 0;
            
        } finally {
            
            try {
                if (commit) {
                    con.commit();
                }
                releaseConnection(con);
         
            } catch (SQLException ex) {
                LOGGER.error("Error while committing changes and releasing connection", ex);
                count = 0;
                
            } finally {
                return count;
            }
        }
    }
    
    private void doInsert(String tableName, Map<String, Object> values) throws SQLException {
        Connection con = null;
        
        try {
            con = getConnection();
            doInsertRec(con, tableName, values);
            
        } catch (SQLException ex) {
            throw ex;
            
        } finally {
            releaseConnection(con);
        }
    }

    /**
     * Inserts a single record into a table using the supplied connection.
     * 
     * @param con connection
     * @param tableName table name
     * @param values map of column name : value pairs
     * 
     * @throws SQLException
     */
    private void doInsertRec(Connection con, String tableName, Map<String, Object> values) 
            throws SQLException {

        StringBuilder sb0 = new StringBuilder();
        sb0.append("INSERT INTO ").append(tableName).append(" (");

        StringBuilder sb1 = new StringBuilder("VALUES (");

        final int N = values.size();
        int k = 0;
        for (Map.Entry<String, Object> e : values.entrySet()) {
            String delim = (++k == N ? ") " : ", ");
            sb0.append(e.getKey()).append(delim);

            Object v = e.getValue();
            if (v instanceof String) {
                sb1.append('"').append(v).append('"');

            } else if (v instanceof Boolean) {
                int b = ((Boolean) v) ? 1 : 0;
                sb1.append(b);

            } else {
                sb1.append(v);
            }
            sb1.append(delim);

        }

        String sql = sb0.append(sb1).toString();
        con.createStatement().executeUpdate(sql);
    }
    
    private void createTables() throws SQLException {
        Connection con = null;
        try {
            con = getConnection();
            con.setAutoCommit(false);
            Statement stmt = con.createStatement();
            
            for (TableDef def : tableDefs) {
                stmt.executeUpdate("drop table if exists " + def.getName());
                stmt.executeUpdate(def.getCreateTableSQL());
            }
            
            con.commit();
            
        } finally {
            releaseConnection(con);
        }
    }
    
    private Connection getConnection() throws SQLException {
        connectionLock.lock();
        try {
            Connection con = connectionPool.poll();
            if (con == null) {
                con = DriverManager.getConnection("jdbc:sqlite:" + dbPath.getPath());
            }
            
            return con;
            
        } finally {
            connectionLock.unlock();
        }
    }

    private void releaseConnection(Connection con) throws SQLException {
        connectionLock.lock();
        try {
            if (con != null && !con.isClosed()) {
                con.setAutoCommit(true);
                connectionPool.offer(con);
            }
        } finally {
            connectionLock.unlock();
        }
    }

    private void closeConnections() throws SQLException {
        connectionLock.lock();
        try {
            Connection con;
            while ((con = connectionPool.poll()) != null) {
                con.close();
            }
        } finally {
            connectionLock.unlock();
        }
    }

    private Optional<TableDef> getTableDef(String tableName) {
        for (TableDef def : tableDefs) {
            if (def.getName().equalsIgnoreCase(tableName)) {
                return Optional.of(def);
            }
        }
        
        LOGGER.error("No table matches name: {}", tableName);
        return Optional.absent();
    }

    private Optional<Map<String, Object>> createValueMap(TableDef tblDef, Object[] values) {
        BP.notNull("tblDef", tblDef);
        BP.arrayNotNullOrEmpty("values", values);
        
        if (tblDef.getNumFields() != values.length) {
            LOGGER.error("Wrong number of values for table {}: got {} but required {}",
                        new Object[]{tblDef.getName(), values.length, tblDef.getNumFields()});
            
            return Optional.absent();
        }
        
        Map<String, Object> map = new LinkedHashMap<String, Object>();
        for (int i = 0; i < values.length; i++) {
            map.put(tblDef.getField(i).getName(), values[i]);
        }
        
        return Optional.of(map);
    }

    private boolean checkValuesAgainstTableDef(TableDef tblDef, Map<String, Object> valueMap) {
        String tblName = tblDef.getName();
        
        if (valueMap.size() > tblDef.getNumFields()) {
            LOGGER.error("Too many values supplied for table {}; expected {} but got {}",
                    new Object[]{tblName, tblDef.getNumFields(), valueMap.size()});
            return false;
        }
        
        for (Map.Entry<String, Object> e : valueMap.entrySet()) {
            String key = e.getKey();
            
            Optional<Field> op = tblDef.getField(key);
            if (!op.isPresent()) {
                LOGGER.error("No column with name {} in table {}", key, tblName);
                return false;
            }
            
            Field fld = op.get();
            Object value = e.getValue();
            if (!fld.isCompatibleType(value)) {
                LOGGER.error("{} is not compatible with field {} of type {}",
                        new Object[]{value.getClass().getName(), key, fld.getType().getTypeName()});
                return false;
            }
        }
        
        return true;
    }

}
